package com.flymobi.fw.dao;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Vector;

import org.apache.log4j.Logger;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.transform.ResultTransformer;
import org.hibernate.transform.Transformers;
import org.springframework.dao.DataAccessException;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.flymobi.fw.orm.Persistent;
import com.flymobi.fw.util.Config;

/**
 * 
 * 
 * Title:
 * 
 * Description:
 * 
 * Copyright: Copyright (c) 2008
 * 
 * @author fly
 * @created Mar 7, 2008 1:29:52 PM
 * @version $Id: BaseHibernateDao.java,v 1.1 2008/03/26 06:24:09 airsou Exp $
 */
public abstract class BaseHibernateDao extends HibernateDaoSupport implements
		BaseDao {

	private static Logger log = Logger.getLogger(BaseHibernateDao.class);

	private static final int QUERY_INFO_MS = Config.getInt(
			"sys.query.info.sec", 3) * 1000;

	private static final int QUERY_WARN_MS = Config.getInt(
			"sys.query.warn.sec", 5) * 1000;

	public void flushThenClearCache() {
		getHibernateTemplate().flush();
		getHibernateTemplate().clear();
	}

	public final <T extends Persistent> T load(final Class<T> objClass,
			final Serializable id) throws DAOException {
		T entity = null;
		try {
			entity = (T) getHibernateTemplate().load(objClass, id);
		} catch (DataAccessException e) {
			e.printStackTrace();
			log.debug("load entity error: " + e);
			return null;
		} catch (Exception e) {
			log.debug("load entity error: " + e);
			return null;
		}

		return entity;
	}

	public final <T extends Persistent> T get(final Class<T> objClass,
			final Serializable id) throws DAOException {
		T entity = null;
		try {
			entity = (T) getHibernateTemplate().get(objClass, id);
		} catch (DataAccessException e) {
			e.printStackTrace();
			log.debug("get entity error: " + e);
			return null;
		} catch (Exception e) {
			log.debug("get entity error: " + e);
			return null;
		}

		return entity;
	}

	/**
	 * Find some objects from database
	 * 
	 * @param query
	 * 
	 * @return entity array
	 */
	@SuppressWarnings("unchecked")
	public final List find(final String hql, final Object[] args)
			throws DAOException {
		long t = System.currentTimeMillis();
		try {
			List list = null;
			if (args == null) {
				list = getHibernateTemplate().find(hql);
			} else {
				list = getHibernateTemplate().find(hql, args);
			}
			long time = System.currentTimeMillis() - t;
			logQueryTime(hql, time);
			return list;
		} catch (DataAccessException e) {
			e.printStackTrace();
			log.error("Find objects from database error:" + e);
			throw new DAOException(e);
		} catch (Throwable e) {

			log.error("Find objects from database error:" + e);
			throw new DAOException(e);
		}
	}

	@SuppressWarnings("unchecked")
	public <T> List<T> find(Class<T> clazz, final String hql,
			final Object[] args) throws DAOException {
		return (List<T>) this.find(hql, args);
	}

	public <T> T findSingle(Class<T> clazz, final String hql,
			final Object[] args) throws DAOException {
		T p = null;
		List<T> list = this.find(clazz, hql, args, 0, 1);
		if (list != null && list.size() > 0) {
			p = list.get(0);
		}
		return p;
	}
	
	public <T> T findSingle(Class<T> clazz, final String hql,
			final List<Object> args) throws DAOException {
		T p = null;
		List<T> list = this.find(clazz, hql, args, 0, 1);
		if (list != null && list.size() > 0) {
			p = list.get(0);
		}
		return p;
	}
	

	public <T> T findSingleBySql(Class<T> clazz, final String nativePSql,
			final Object[] args) throws DAOException {

		T p = null;
		List<T> list = this.executeQueryBySQL(clazz, nativePSql, null, args);
		if (list != null && list.size() > 0) {
			p = list.get(0);
		}
		return p;
	}

	/**
	 * Find some objects from database
	 * 
	 * @param query
	 * 
	 * @return entity array
	 */
	@SuppressWarnings("unchecked")
	public final List find(final String hql, final Object[] args,
			final int startRows, final int rows) throws DAOException {
		return this.find(Object.class, hql, args, startRows, rows);
	}

	public <T> List<T> find(Class<T> clazz, final String hql,
			final Object[] args, final int startRows, final int rows)
			throws DAOException {
		try {
			long t = System.currentTimeMillis();
			List<T> list = null;
			list = (List<T>) getHibernateTemplate().execute(
					new HibernateCallback<List<T>>() {
						@SuppressWarnings("unchecked")
						public List<T> doInHibernate(Session session)
								throws HibernateException {
							Query query = session.createQuery(hql);
							// prepareQuery(queryObject);
							if (args != null && args.length > 0) {
								int l = args.length;
								for (int i = 0; i < l; i++) {
									query.setParameter(i, args[i]);
								}
							}
							if (rows > 0) {
								query.setMaxResults(rows);
							}
							query.setFirstResult(startRows);
							return query.list();
						}
					});
			long time = System.currentTimeMillis() - t;
			logQueryTime(hql, time);
			return list;
		} catch (DataAccessException e) {
			e.printStackTrace();
			log.error("Find objects from database error:" + e);
			throw new DAOException(e);
		} catch (Throwable e) {
			log.error("Find objects from database error:" + e);
			throw new DAOException(e);
		}
	}
	
	public <T> List<T> find(Class<T> clazz, final String hql,
			final List<Object> args, final int startRows, final int rows)
			throws DAOException {
		try {
			long t = System.currentTimeMillis();
			List<T> list = null;
			list = (List<T>) getHibernateTemplate().execute(
					new HibernateCallback<List<T>>() {
						@SuppressWarnings("unchecked")
						public List<T> doInHibernate(Session session)
								throws HibernateException {
							Query query = session.createQuery(hql);
							// prepareQuery(queryObject);
							if (args != null && args.size() > 0) {
								int l = args.size();
								for (int i = 0; i < l; i++) {
									query.setParameter(i, args.get(i));
								}
							}
							if (rows > 0) {
								query.setMaxResults(rows);
							}
							query.setFirstResult(startRows);
							return query.list();
						}
					});
			long time = System.currentTimeMillis() - t;
			logQueryTime(hql, time);
			return list;
		} catch (DataAccessException e) {
			e.printStackTrace();
			log.error("Find objects from database error:" + e);
			throw new DAOException(e);
		} catch (Throwable e) {
			log.error("Find objects from database error:" + e);
			throw new DAOException(e);
		}
	}

	public int executeUpdate(final String hql, final Object[] args)
			throws DAOException {
		try {
			long t = System.currentTimeMillis();
			int result = (Integer) getHibernateTemplate().execute(
					new HibernateCallback<Object>() {
						public Object doInHibernate(Session session)
								throws HibernateException {
							Query query = session.createQuery(hql);
							if (args != null && args.length > 0) {
								int l = args.length;
								for (int i = 0; i < l; i++) {
									query.setParameter(i, args[i]);
								}
							}
							return query.executeUpdate();
						}
					});
			long time = System.currentTimeMillis() - t;
			logQueryTime(hql, time);
			return result;
		} catch (DataAccessException e) {
			e.printStackTrace();
			log.error("Find objects from database error:" + e);
			throw new DAOException(e);
		} catch (Throwable e) {
			log.error("Find objects from database error:" + e);
			throw new DAOException(e);
		}
	}

	@SuppressWarnings("unchecked")
	public String getIds(final String hql, final Object[] args,
			final int startRow, final int rows) {
		StringBuffer ret = new StringBuffer();
		List<Persistent> res = (List<Persistent>) this.find(hql, args,
				startRow, rows);
		for (Persistent p : res) {
			if (ret.length() > 0)
				ret.append(",");
			ret.append(p.getId());
		}
		return ret.toString();
	}

	@SuppressWarnings("unchecked")
	public String getIds(final String hql, final Object[] args) {
		StringBuffer ret = new StringBuffer();
		List<Persistent> res = (List<Persistent>) this.find(hql, args);
		for (Persistent p : res) {
			if (ret.length() > 0)
				ret.append(",");
			ret.append(p.getId());
		}
		return ret.toString();
	}

	/**
	 * get count from database
	 * 
	 * @param query
	 * 
	 * @return entity array
	 */
	public Long getCount(final String hql, final List<Object> args)
			throws DAOException {
		Long l = this.findSingle(Long.class, hql, args);
		if (l == null) {
			return new Long(0);
		}
		return l;
	}
	
	public Long getCount(final String hql, final Object[] args)
	throws DAOException {
		Long l = this.findSingle(Long.class, hql, args);
		if (l == null) {
			return new Long(0);
		}
		return l;
	}

	@SuppressWarnings("unchecked")
	public long getSeq(final String sql, final Object[] args)
			throws DAOException {
		List<Object[]> l = this.executeQueryBySQL(sql, null, args);
		if (l == null||l.size()==0) {
			return 1;
		}
		if(l.get(0)[0]==null){
			return 1;
		}
		return Long.valueOf(l.get(0)[0].toString());
	}

	/**
	 * update a object from database
	 * 
	 * @param obj
	 *            Object
	 * 
	 */
	public final <T extends Persistent> T update(T obj) throws DAOException {

		try {
			// Date now = new Date();
			// obj.setSavetime(now);
			obj.setUpdatetime(new Date());
			getHibernateTemplate().update(obj);
		} catch (DataAccessException e) {
			e.printStackTrace();
			log.error("update entity error: " + e);
			throw new DAOException(e);
		} catch (Throwable e) {
			log.error("update entity error: " + e);
			throw new DAOException(e);
		}
		return obj;
	}

	/**
	 * update a object from database
	 * 
	 * @param obj
	 *            Object
	 * 
	 */
	public final <T extends Persistent> T updateByNoTime(T obj)
			throws DAOException {

		try {
			getHibernateTemplate().update(obj);
		} catch (DataAccessException e) {
			e.printStackTrace();
			log.error("update entity error: " + e);
			throw new DAOException(e);
		} catch (Throwable e) {

			log.error("update entity error: " + e);
			throw new DAOException(e);
		}

		return obj;
	}

	/**
	 * Remove a object from database
	 * 
	 * @param obj
	 *            Object
	 * 
	 */
	public final void delete(final Persistent obj) throws DAOException {

		try {
			if (obj != null) {
				getHibernateTemplate().delete(obj);
			}
		} catch (DataAccessException e) {
			e.printStackTrace();
			log.error("remove entity error: " + e);
			throw new DAOException(e);
		} catch (Throwable e) {
			log.error("remove entity error: " + e);
			throw new DAOException(e);
		}
	}

	/**
	 * insert a object into database
	 * 
	 * @param obj
	 * 
	 */
	public final Number insert(Persistent obj) throws DAOException {

		try {
			Date now = new Date();
			if (obj.getSavetime() == null) {
				obj.setSavetime(now);
			}
			return ((Number) getHibernateTemplate().save(obj)).longValue();
		} catch (DataAccessException e) {
			e.printStackTrace();
			log.error("insert entity error: " + e);
			throw new DAOException(e);
		} catch (Throwable e) {
			e.printStackTrace();
			log.error("insert entity error: " + e);
			throw new DAOException(e);
		}
	}

	public void save(final Persistent obj) throws DAOException {
		obj.setUpdatetime(new Date());
		if (obj.getId() == null || obj.getId().equals(new Long(0))) {
			this.insert(obj);
		} else {
			this.update(obj);
		}
	}

	/**
	 * insert a object into database
	 * 
	 * @param obj
	 * 
	 */
	public final Object insertByNoTime(Persistent obj) throws DAOException {
		try {
			return getHibernateTemplate().save(obj);
		} catch (DataAccessException e) {
			log.error("insert entity error: " + e);
			throw new DAOException(e);
		} catch (Throwable e) {
			e.printStackTrace();
			log.error("insert entity error: " + e);
			throw new DAOException(e);
		}
	}

	public int executeBySQL(final String naturePSQL) {
		return executeBySQL(naturePSQL, null, null);
	}

	public void truncateTable(final String tableName) {
		String sql = "truncate table " + tableName;
		executeBySQL(sql);
	}

	/**
	 * @param naturePSQL
	 * @param params
	 * @throws DataAccessException
	 */
	public final int executeBySQL(final String naturePSQL, final Object[] params)
			throws DAOException {
		return executeBySQL(naturePSQL, null, params);
	}

	/**
	 * @param naturePSQL
	 * @param types
	 * @param params
	 * @throws DataAccessException
	 */
	@SuppressWarnings("unchecked")
	public final int executeBySQL(final String naturePSQL, final int[] types,
			final Object[] params) throws DAOException {
		try {
			Integer updateCount = (Integer) getHibernateTemplate().execute(
					new HibernateCallback() {

						@SuppressWarnings("deprecation")
						public Object doInHibernate(Session session)
								throws HibernateException, SQLException {
							long t = System.currentTimeMillis();
							Connection c = session.connection();
							PreparedStatement pst = null;
							int result = 0;
							try {
								pst = c.prepareStatement(naturePSQL);
								if (types != null && params != null) {
									StatementUtils.setParameterValue(pst,
											types, params);

								} else if (params != null) {
									StatementUtils.setParameterValue(pst,
											params);
								}
								result = pst.executeUpdate();
							} finally {
								try {
									pst.close();
									pst = null;
								} catch (Exception e) {
									log.warn("Could not close JDBC Statement",
											e);
								}
							}
							long time = System.currentTimeMillis() - t;
							logQueryTime(naturePSQL, time);
							return new Integer(result);
						}
					});
			return updateCount.intValue();
		} catch (DataAccessException e) {
			e.printStackTrace();
			log.error("executeBySQL error: " + e);
			throw new DAOException(e);
		}
	}

	/**
	 * @param naturePSQL
	 * @param types
	 * @param params
	 * @throws DataAccessException
	 */
	@SuppressWarnings("unchecked")
	public Long getCountQueryBySQL(final String naturePSQL, final int[] types,
			final Object[] params) throws DAOException {
		long t = System.currentTimeMillis();
		Long count = new Long(-1);
		List re = this.executeQueryBySQL(naturePSQL, types, params);
		if (re != null && re.size() > 0) {
			count = new Long(((Object[]) re.get(0))[0] + "");
		}
		long time = System.currentTimeMillis() - t;
		logQueryTime(naturePSQL, time);
		return count;
	}

	/**
	 * @param naturePSQL
	 * @param types
	 * @param params
	 * @throws DataAccessException
	 */
	@SuppressWarnings("unchecked")
	public List executeQueryBySQL(final String naturePSQL, final int[] types,
			final Object[] params) throws DAOException {
		log.info(naturePSQL);
		try {
			List list = (List) getHibernateTemplate().execute(
					new HibernateCallback() {

						@SuppressWarnings("deprecation")
						public Object doInHibernate(Session session)
								throws HibernateException, SQLException {
							long t = System.currentTimeMillis();
							Connection c = session.connection();
							PreparedStatement pst = null;
							List<Object[]> result = new Vector<Object[]>();
							ResultSet rs = null;
							try {
								pst = c.prepareStatement(naturePSQL);
								if (types != null && params != null) {
									StatementUtils.setParameterValue(pst,
											types, params);

								} else if (params != null) {
									StatementUtils.setParameterValue(pst,
											params);
								}

								rs = pst.executeQuery();
								ResultSetMetaData rsmd = rs.getMetaData();
								int colum = rsmd.getColumnCount();
								int[] type = new int[colum + 1];
								for (int i = 1; i <= colum; i++) {
									type[i] = rsmd.getColumnType(i);
								}
								while (rs.next()) {
									Object[] values = new Object[colum];
									for (int i = 1; i <= colum; i++) {
										switch (type[i]) {
										case Types.NUMERIC:
											values[i - 1] = new Long(rs
													.getInt(i));
											break;
										case Types.VARCHAR:
											values[i - 1] = rs.getString(i);
											break;
										case Types.DATE:
											values[i - 1] = rs.getDate(i);
											break;
										case Types.TIMESTAMP:
											values[i - 1] = rs.getTimestamp(i);
											break;
										default:
											values[i - 1] = rs.getObject(i);
											break;
										}
									}
									result.add(values);
								}

							} finally {
								try {
									pst.close();
									pst = null;
									rs.close();
								} catch (Exception e) {
									log.warn("Could not close JDBC Statement",
											e);
								}
							}
							long time = System.currentTimeMillis() - t;
							logQueryTime(naturePSQL, time);
							return result;
						}
					});
			return list;
		} catch (DataAccessException e) {
			e.printStackTrace();
			log.error("executeBySQL error: " + e);
			throw new DAOException(e);
		}
	}
	
	
	@SuppressWarnings("unchecked")
	public List<Map<String,Object>> executeQueryBySQL2(final String naturePSQL, final int[] types,
			final Object[] params) throws DAOException {
		log.info(naturePSQL);
		try {
			List<Map<String,Object>> list = (List<Map<String,Object>>) getHibernateTemplate().execute(
					new HibernateCallback() {

						@SuppressWarnings("deprecation")
						public Object doInHibernate(Session session)
								throws HibernateException, SQLException {
							long t = System.currentTimeMillis();
							Connection c = session.connection();
							PreparedStatement pst = null;
							List<Map<String,Object>> result = new ArrayList<Map<String,Object>>();
							ResultSet rs = null;
							try {
								pst = c.prepareStatement(naturePSQL);
								if (types != null && params != null) {
									StatementUtils.setParameterValue(pst,
											types, params);

								} else if (params != null) {
									StatementUtils.setParameterValue(pst,
											params);
								}

								rs = pst.executeQuery();
								ResultSetMetaData rsmd = rs.getMetaData();
								int colum = rsmd.getColumnCount();
								String[] columnName=new String[colum + 1];
								for (int i = 1; i <=colum; i++) {
									columnName[i-1]=rsmd.getColumnName(i);
								}
								while (rs.next()) {
									Map<String,Object> values = new HashMap<String,Object>();
									for (int i = 1; i <= colum; i++) {
										values.put(columnName[i-1], rs.getObject(i));
									}
									result.add(values);
								}

							} finally {
								try {
									pst.close();
									pst = null;
									rs.close();
								} catch (Exception e) {
									log.warn("Could not close JDBC Statement",
											e);
								}
							}
							long time = System.currentTimeMillis() - t;
							logQueryTime(naturePSQL, time);
							return result;
						}
					});
			return list;
		} catch (DataAccessException e) {
			e.printStackTrace();
			log.error("executeBySQL error: " + e);
			throw new DAOException(e);
		}
	}

	@SuppressWarnings("unchecked")
	public <T> List<T> executeQueryBySQL(final Class<T> resultClass,
			final String naturePSQL, final Object[] parameters)
			throws DAOException {
		List<T> list = (List<T>) getHibernateTemplate().execute(
				new HibernateCallback() {
					public List<T> doInHibernate(Session session)
							throws HibernateException, SQLException {
						long t = System.currentTimeMillis();
						SQLQuery query = session.createSQLQuery(naturePSQL);
						ResultTransformer rsTransformer = Transformers
								.aliasToBean(resultClass);
						query.setResultTransformer(rsTransformer);
						if (parameters != null && parameters.length > 0) {
							for (int i = 0; i < parameters.length; i++) {
								query.setParameter(i, parameters[i]);
							}
						}
						long time = System.currentTimeMillis() - t;
						logQueryTime(naturePSQL, time);
						return (List<T>) query.list();
					}
				});
		return list;
	}

	@SuppressWarnings("unchecked")
	public List executeQueryBySQL(final String naturePSQL, final Object[] params)
			throws DAOException {
		return this.executeQueryBySQL(naturePSQL, null, params);
	}

	@SuppressWarnings("unchecked")
	public <T> List<T> executeQueryBySQL(Class<T> clazz,
			final String naturePSQL, final int[] types, final Object[] params)
			throws DAOException {
		return (List<T>) this.executeQueryBySQL(naturePSQL, types, params);
	}

	/**
	 * 
	 * @param hql
	 * @param args
	 * @return
	 * @throws DAOException
	 */
	@SuppressWarnings( { "unchecked" })
	public final int executeByHQL(final String hql, final Object[] args)
			throws DAOException {
		try {
			long t = System.currentTimeMillis();
			Integer ret = (Integer) getHibernateTemplate().execute(
					new HibernateCallback() {
						public Object doInHibernate(Session session)
								throws HibernateException {
							Query query = session.createQuery(hql);
							if (args != null && args.length > 0) {
								int l = args.length;
								for (int i = 0; i < l; i++) {
									query.setParameter(i, args[i]);
								}
							}
							int ret = query.executeUpdate();
							return ret;
						}
					});
			long time = System.currentTimeMillis() - t;
			logQueryTime(hql, time);
			return ret;
		} catch (DataAccessException e) {
			e.printStackTrace();
			log.error("Find objects from database error:" + e);
			throw new DAOException(e);
		} catch (Throwable e) {
			log.error("Find objects from database error:" + e);
			throw new DAOException(e);
		}
	}

	public <T> int saveList(final List<T> list) {
		try {
			long t = System.currentTimeMillis();
			Integer ret = (Integer) getHibernateTemplate().execute(
					new HibernateCallback<Integer>() {
						public Integer doInHibernate(Session session) {
							int rows = 0;
							boolean state = true;
							for (T t : list) {
								state = true;
								session.save(t);
								rows++;
								if (rows % 50 == 0) {
									session.flush();
									session.clear();
									state = false;
								}
							}
							if (state) {
								session.flush();
								session.clear();
							}
							return new Integer(rows);
						}
					});
			long time = System.currentTimeMillis() - t;
			logQueryTime("insert list...", time);
			return ret;
		} catch (DataAccessException e) {
			e.printStackTrace();
			log.error("save objects into database error:" + e);
			throw new DAOException(e);
		} catch (Throwable e) {
			log.error("save objects into database error:" + e);
			throw new DAOException(e);
		}
	}

	private void logQueryTime(String query, long time) {
		String content = time + " " + query;
		if (time > QUERY_WARN_MS) {
			log.warn(content);
		} else if (time > QUERY_INFO_MS) {
			log.info(content);
		} else {
			log.debug(content);
		}
	}
}